package com.mmy.dao.scenic;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.scenic.VScenicBase;

public interface VScenicBaseRepository extends JpaRepository<VScenicBase, Integer> {

	/*
	 * 根据name查找 模糊
	 */
	@Query(value = "select *  from vScenicBase where cScenicName like %:strScenicName%", nativeQuery = true)
	List<VScenicBase> findByNamel(@Param("strScenicName") String strScenicName);

	/*
	 * 根据name查找 精确
	 */
	@Query(value = "select *  from vScenicBase where cScenicName = :strScenicName", nativeQuery = true)
	List<VScenicBase> findByName(@Param("strScenicName") String strScenicName);

	/*
	 * 根据id查找
	 */
	@Query(value = "select *  from vScenicBase where iScenicID = :iScenicID", nativeQuery = true)
	VScenicBase findByID(@Param("iScenicID") int iScenicID);

	/**
	 * 函数功能说明： 跳转 进入后台景区电子票信息页面 查询该公司代理的所有景区 以创建时间倒叙排列
	 *
	 * @throws 异常处理说明
	 * @author 创建人 hanxl
	 * @param iPageSize
	 * @param iTempPageNo
	 * @param strScenicName
	 * @date 创建日期2020.12.24
	 */
	@Query(value = "select *  from vScenicBase where (:strScenicName = \"\" or cScenicName like %:strScenicName%) and cSpare1 = :iOrgID "
			+ "and  (:strScenicContact = \"\" or cContact like %:strScenicContact%) "
			+ "and (:strScenicTel = \"\" or cTel like %:strScenicTel%) "
			+ "and (:strProvinceID = \"\" or left(iZoneID,2) =left(:strProvinceID,2)) "
			+ "and (:strCityID = \"\" or left(iZoneID,4) =left(:strCityID,4)) "
			+ "and (:strZoneID = \"\" or iZoneID =:strZoneID)  order by dCreateDate desc limit :iTempPageNo,:iPageSize", nativeQuery = true)
	List<VScenicBase> findByOrgID(@Param("strScenicName") String strScenicName, @Param("iOrgID") int iOrgID,
			@Param("strScenicContact") String strScenicContact, @Param("strScenicTel") String strScenicTel,
			@Param("strProvinceID") String strProvinceID, @Param("strCityID") String strCityID,
			@Param("strZoneID") String strZoneID, @Param("iTempPageNo") int iTempPageNo,
			@Param("iPageSize") int iPageSize);

	/**
	 * 后台景区电子票管理中超级管理员登录显示所有景点信息
	 *
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.12.24
	 */
	@Query(value = "select * from vScenicBase where (:strScenicName = \"\" or cScenicName like %:strScenicName%) "
			+ "and  (:strScenicContact = \"\" or cContact like %:strScenicContact%) "
			+ "and (:strScenicTel = \"\" or cTel like %:strScenicTel%) "
			+ "and (:strProvinceID = \"\" or left(iZoneID,2) =left(:strProvinceID,2)) "
			+ "and (:strCityID = \"\" or left(iZoneID,4) =left(:strCityID,4)) "
			+ "and (:strZoneID = \"\" or iZoneID =:strZoneID)  order by dCreateDate desc limit :iTempPageNo,:iPageSize", nativeQuery = true)
	List<VScenicBase> findAll(@Param("strScenicName") String strScenicName,
			@Param("strScenicContact") String strScenicContact, @Param("strScenicTel") String strScenicTel,
			@Param("strProvinceID") String strProvinceID, @Param("strCityID") String strCityID,
			@Param("strZoneID") String strZoneID, @Param("iTempPageNo") int iTempPageNo,
			@Param("iPageSize") int iPageSize);

	/**
	 * 后台景区电子票管理中超级管理员登录显示所有公司的景区信息 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.11.19
	 */
	@Query(value = "select count(*)  from vScenicBase where (:strScenicName = \"\" or cScenicName like %:strScenicName%) "
			+ "and  (:strScenicContact = \"\" or cContact like %:strScenicContact%) "
			+ "and (:strScenicTel = \"\" or cTel like %:strScenicTel%) "
			+ "and (:strProvinceID = \"\" or left(iZoneID,2) =left(:strProvinceID,2)) "
			+ "and (:strCityID = \"\" or left(iZoneID,4) =left(:strCityID,4)) "
			+ "and (:strZoneID = \"\" or iZoneID =:strZoneID)  order by dCreateDate desc", nativeQuery = true)
	int countALL(@Param("strScenicName") String strScenicName, @Param("strScenicContact") String strScenicContact,
			@Param("strScenicTel") String strScenicTel, @Param("strProvinceID") String strProvinceID,
			@Param("strCityID") String strCityID, @Param("strZoneID") String strZoneID);

	/**
	 * 后台景区电子票管理中查询该公司代理的所有景区 获取分页
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.11.19
	 */
	@Query(value = "select count(*)  from vScenicBase where (:strScenicName = \"\" or cScenicName like %:strScenicName%) and cSpare1=:iOrgID "
			+ "and  (:strScenicContact = \"\" or cContact like %:strScenicContact%) "
			+ "and (:strScenicTel = \"\" or cTel like %:strScenicTel%) "
			+ "and (:strProvinceID = \"\" or left(iZoneID,2) =left(:strProvinceID,2)) "
			+ "and (:strCityID = \"\" or left(iZoneID,4) =left(:strCityID,4)) "
			+ "and (:strZoneID = \"\" or iZoneID =:strZoneID)  order by dCreateDate desc", nativeQuery = true)
	int countByOrgID(@Param("strScenicName") String strScenicName, @Param("iOrgID") int iOrgID,
			@Param("strScenicContact") String strScenicContact, @Param("strScenicTel") String strScenicTel,
			@Param("strProvinceID") String strProvinceID, @Param("strCityID") String strCityID,
			@Param("strZoneID") String strZoneID);

	/**
	 * 函数功能说明： 进入后台景区电子票信息页面查询景点详情信息
	 *
	 * @return 返回值说明 jsp页面
	 * @throws 异常处理说明
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.12.24
	 */
	@Query(value = "select * from vScenicBase where iScenicID=:iScenicID ", nativeQuery = true)
	VScenicBase findByScenicID(@Param("iScenicID") int iScenicID);

	/**
	 * 前台景区电子票菜单显示景区信息
	 * 
	 * @return 返回值说明
	 * @author 创建人hanxl
	 * @param iPageSize
	 * @param iTempPageNo
	 * @date 创建日期 2020.12.24
	 */
	@Query(value = "(select * from vScenicBase where iScenicID in(:lstScenicID) and cScenicName like %:cScenicName%  and left(iZoneID,4)=left(:iZoneID,4) ) "
			+ "union (select * from vScenicBase where iScenicID in(:lstScenicID) and cScenicName like %:cScenicName%  ) "
			+ "union (select * from vScenicBase where iScenicID in(:lstScenicID) and left(iZoneID,4)=left(:iZoneID,4) ) "
			+ "union (select * from vScenicBase where iScenicID in(:lstScenicID)) limit :iTempPageNo,:iPageSize", nativeQuery = true)
	List<VScenicBase> findBySearch(@Param("lstScenicID") List<Integer> lstScenicID, @Param("iZoneID") int iZoneID,
			@Param("cScenicName") String strScenicName, @Param("iTempPageNo") int iTempPageNo,
			@Param("iPageSize") int iPageSize);

	/**
	 * 前台景区电子票菜单显示景区信息 分页
	 * 
	 * @return 返回值说明
	 * @author 创建人hanxl
	 * @param iPageSize
	 * @param iTempPageNo
	 * @date 创建日期 2020.12.24
	 */
	@Query(value = "select count(*)  from vScenicBase where iScenicID in(:lstScenicID)", nativeQuery = true)
	int countSearch(List<Integer> lstScenicID);

	/**
	 * 函数功能说明：前台首页景区模块显示 前8条
	 *
	 * @param 参数说明
	 * @return 返回值说明 jsp页面
	 * @throws 异常处理说明
	 * @author 创建人hanxl
	 * @date 创建日期2021.02.23
	 */
	@Query(value = "select * from vScenicBase where iScenicID in(:lstScenicID) and left(iZoneID,2)=left(:iZoneID,2) order by dCreateDate desc limit 8;", nativeQuery = true)
	List<VScenicBase> findScenic(List<Integer> lstScenicID,@Param("iZoneID") int iZoneID);

}
